<?php

class Database_forge_model extends CI_Model{
	
	function __construct(){
		$this->load->dbforge();
	}
	
	function createDatabase(){	
		// Will not work due to playback being specified as datbase in the config file
		if ($this->dbforge->create_database('playback'))
		{
	    	echo 'Database created!';
		}
	}
	
	function dropDatabase(){
	if ($this->dbforge->drop_database('playback'))
		{
		    echo 'Database deleted!';
		}
	}
	
	function createAllTables(){
		$this->createArticlesTable();
		$this->createArticleCategoriesTable();
		$this->createUserOpenIdTable();
		$this->createArticleCommentsTable();
		$this->createBookmarkTable();
		$this->createSubscriptionTable();
		
		$this->addCategories();
		
		echo "Ran createAllTables!";
	}
	
	function createArticlesTable(){
		
		// Create the articles -table
		// in case of update to ./system/database/drivers/mysql/mysql_forge.php see
		// https://bitbucket.org/ellislab/codeigniter/issue/303/database-forge-class-current_timestamp-as
		$fields = array(
	                        'articleID' => 	array(
	                                          'type' => 'INT',
	                                          'constraint' => 255,
	                                          'unsigned' => TRUE,
	                                          'auto_increment' => TRUE,
	                                          ),
	                        'categoryID' => array(
	                                          'type' => 'INT',
	                                          'constraint' => 255,
	                                          'unsigned' => TRUE,
	                                          ),
	                        'userID' => 	array(
	                                          'type' => 'INT',
	                                          'constraint' => 255,
	                                          'unsigned' => TRUE,
	                                          ),
	                        'title' => 		array(
	                                          'type' => 'VARCHAR',
	                                          'constraint' => 127,
	                                          ),
	                        'text' => 		array(
	                                          'type' => 'TEXT',
	                                          ),
	                        'imgRaw' =>		array(
	                                          'type' => 'VARCHAR',
	                                          'constraint' => 255,
	                                          ),
	                        'imgExt' =>		array(
	                                          'type' => 'VARCHAR',
	                                          'constraint' => 7,
	                                          ),
	                        'ratingSum' => 	array(
	                                          'type' => 'INT',
	                                          'constraint' => 255,
	                                          'default' => 0,
	                                          'unsigned' => TRUE,
	                                          ),
							'ratingNo' => 	array(
	                                          'type' => 'INT',
	                                          'constraint' => 255,
	                                          'default' => 0,
	                                          'unsigned' => TRUE,
	                                          ),
	                        'ratingAvg' => 	array(
	                                          'type' => 'DOUBLE',
	                                          'default' => 0,
	                                          'unsigned' => TRUE,
	                                          ),
	                        'date' => 		array(
	                                         'type' => 'TIMESTAMP',
	                                          'default' => "CURRENT_TIMESTAMP",
	                                          ),
	                );	
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('articleID', TRUE);
		$this->dbforge->create_table('articles', TRUE);
		
		echo "Ran createArticlesTable!";
	}
		
	function createArticleCategoriesTable(){
		// Create the article_categories -table
		$fields = array(
                        'articleCategoryID' => 	array(
                                          'type' => 'INT',
                                          'constraint' => 255,
                                          'unsigned' => TRUE,
                                          'auto_increment' => TRUE,
                                          ),
                        'name' => 		array(
                                          'type' => 'VARCHAR',
                                          'constraint' => 127,
                                          ),
                        'url' =>		array(
                                          'type' => 'VARCHAR',
                                          'constraint' => 255,
                                          ),

                );
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('articleCategoryID', TRUE);
		$this->dbforge->create_table('article_categories', TRUE);
		
		echo "Ran createArticleCategoriesTable!";
	}
	
	function createUserOpenIdTable(){
		$fields = array(
					'openidURL'=> array(
									'type' => 'VARCHAR',
									'constraint' => '255'),
					'userID'=>	   array(
									'type' => 'INT',
									'constraint' => 255,
									'auto_increment'=>TRUE,
									'unsigned' => TRUE),
					'username'=>	array(
									 'type'=> 'VARCHAR',
									 'constraint' => '255'),
					'email'=>		array(
									 'type'=>'VARCHAR',
									 'constraint'=>'255'),
					'public_openid'=>array(
									  'type'=>'BOOLEAN',
							  	  	  'default'=>0),
					'public_email'=>array(
									  'type'=>'BOOLEAN',
									  'default'=>0));
		
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('openidURL', TRUE);
		$this->dbforge->add_key('userID');
		$this->dbforge->create_table('user_openids');
		
		echo 'Ran createUserTables!';
	}
	
	function createArticleCommentsTable(){
		$fields = array(
					'commentID'=> array(
									'type' => 'INT',
									'constraint' => 255,
									'auto_increment' => TRUE,
									'unsigned' => TRUE),
					'articleID'=> array(
									'type' => 'INT',
									'constraint' => 255,
									'unsigned' => TRUE),
					'userID'=>	  array(
									'type' => 'INT',
	                                'constraint' => 255,
	                                'unsigned' => TRUE),
			        'text' => 	  array(
	                                'type' => 'TEXT'),
			        'date' => 	  array(
		                            'type' => 'TIMESTAMP',
	                                'default' => "CURRENT_TIMESTAMP"));
		
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('commentID', TRUE);
		$this->dbforge->create_table('article_comments');
		
		echo "Ran createArticleCommentsTable!";
	}
	
	function createBookmarkTable(){
		$fields = array('userID'=>	  array(
									'type' => 'INT',
	                                'constraint' => 255,
	                                'unsigned' => TRUE),
						'articleID'=> array(
									'type' => 'INT',
									'constraint' => 255,
									'unsigned' => TRUE)
		);
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('userid');
		$this->dbforge->create_table('article_bookmarks');
		
		echo "Bookmark table created!";
	}
	
	function createSubscriptionTable(){
		$fields = array('userID'=>	  array(
									'type' => 'INT',
	                                'constraint' => 255,
	                                'unsigned' => TRUE),
						'authorID'=> array(
									'type' => 'INT',
									'constraint' => 255,
									'unsigned' => TRUE)
		);
		$this->dbforge->add_field($fields);
		$this->dbforge->add_key('userid');
		$this->dbforge->create_table('subscriptions');
		
		echo "Subscription table created!";
	}
	
	function dropAllTables(){
		dropArticlesTable();
		dropArticleCategoriesTable();
		dropUserOpenIdTable();
		dropArticleCommentsTable();
		dropBookmarkTable();
		dropSubscriptionTable();
		echo "Ran dropAllTables!";
	}
	
	function dropArticlesTable(){
		$this->dbforge->drop_table('articles');
		echo "Ran dropArticlesTable!";
	}
	
	function dropArticleCategoriesTable(){
		$this->dbforge->drop_table('article_categories');
		echo "Ran dropArticleCategoriesTable!";
	}
	
	function dropUserOpenIdTable(){
		$this->dbforge->drop_table('user_openids');
		echo "Ran dropUserOpenIdTable";
	}
	
	function dropArticleCommentsTable(){
		$this->dbforge->drop_table('article_comments');
		echo "Ran dropArticleCommentsTable";
	}
	
	function dropBookmarkTable(){
		$this->dbforge->drop_table('article_bookmarks');
		echo "Ran dropArticleCommentsTable";
	}
	function dropSubscriptionTable(){
		$this->dbforge->drop_table('subscriptions');
		echo "Ran dropArticleCommentsTable";
	}
	
	function addCategories(){
		$data = array(
					array('name'=>'Music', 'url'=>'index.php/articles/categories/music/'),
					array('name'=>'Movies', 'url'=>'index.php/articles/categories/movies/'),
					array('name'=>'Games', 'url'=>'index.php/articles/categories/games/'),
					array('name'=>'TV Series', 'url'=>'index.php/articles/categories/tv_series/'));
		$this->db->insert_batch('article_categories', $data);
	}
	
}